Imports Microsoft.VisualBasic
Imports System
Imports System.Collections.Generic
Imports System.Data.SqlClient
Imports System.Text
Imports System.Data
Namespace WorkFlow.DAO
    Public Class OrgDAO
        Inherits OAMS.DAO.BaseDAO
        Dim _dbName As String
        Public Sub New(ByVal dbName As String)
            _dbName = dbName
        End Sub
        Public Function GetOrg(ByVal OrganizationID As String) As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}
            sqlParams(0) = New SqlParameter("@OrganizationID", SqlDbType.NVarChar)
            sqlParams(0).Value = OrganizationID


            Dim sb As New StringBuilder()



            sb.AppendLine("select * from Organization  ")
            sb.AppendLine("where OrganizationID = @OrganizationID ")

            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
        End Function

        Public Function GetOrgByParent(ByVal Parent As String, ByVal Active As String) As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}
            sqlParams(0) = New SqlParameter("@Parent", SqlDbType.NVarChar)
            sqlParams(0).Value = Parent

            sqlParams(1) = New SqlParameter("@Active", SqlDbType.NVarChar)
            sqlParams(1).Value = Active
            Dim sb As New StringBuilder()



            sb.AppendLine("select * from Organization  ")
            If Parent = "null" Then ''
                sb.AppendLine("where Parent is null  and Active=@Active ")
            Else
                sb.AppendLine("where Parent = @Parent and Active=@Active ")

            End If



            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
        End Function

        Public Function GetNotActive() As DataTable
     

            Dim sb As New StringBuilder()
            sb.AppendLine("Select * from Organization ")
            sb.AppendLine("where active='N' ")
            sb.AppendLine("order by Parent ")

            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString()).Tables(0)
        End Function
        Public Sub UpdateActiveStatus(ByVal Active As String, ByVal OrganizationID As String)
            Dim sqlParams As SqlParameter() = New SqlParameter(2) {}
            sqlParams(0) = New SqlParameter("@Active", SqlDbType.NVarChar)
            sqlParams(0).Value = Active
            sqlParams(1) = New SqlParameter("@OrganizationID", SqlDbType.NVarChar)
            sqlParams(1).Value = OrganizationID
            Dim sb As New StringBuilder()

            sb.AppendLine("update Organization set ")
            sb.AppendLine(" Active = @Active ")
            sb.AppendLine("where OrganizationID = @OrganizationID or Parent=@OrganizationID ")


            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)

        End Sub
        Public Sub ChangeParent(ByVal Parent As String, ByVal OrganizationID As String)
            Dim sqlParams As SqlParameter() = New SqlParameter(2) {}
            sqlParams(0) = New SqlParameter("@Parent", SqlDbType.NVarChar)
            sqlParams(0).Value = Parent
            sqlParams(1) = New SqlParameter("@OrganizationID", SqlDbType.NVarChar)
            sqlParams(1).Value = OrganizationID
            Dim sb As New StringBuilder()

            sb.AppendLine("update Organization set ")
            sb.AppendLine(" Parent = @Parent ")
            sb.AppendLine("where OrganizationID = @OrganizationID ")
            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)
        End Sub
        Public Function InsertOrg(ByVal OrganizationName As String, ByVal Parent As String, ByVal Active As String, ByVal OrgEngName As String, ByVal OrgChnName As String) As Integer
            Dim sqlParams As SqlParameter() = New SqlParameter(5) {}
            sqlParams(0) = New SqlParameter("@OrganizationName", SqlDbType.NVarChar)
            sqlParams(0).Value = OrganizationName
            sqlParams(1) = New SqlParameter("@Parent", SqlDbType.NVarChar)
            sqlParams(1).Value = Parent
            sqlParams(2) = New SqlParameter("@Active", SqlDbType.NVarChar)
            sqlParams(2).Value = Active
            sqlParams(3) = New SqlParameter("@OrgEngName", SqlDbType.NVarChar)
            sqlParams(3).Value = OrgEngName
            sqlParams(4) = New SqlParameter("@OrgChnName", SqlDbType.NVarChar)
            sqlParams(4).Value = OrgChnName


            Dim sb As New StringBuilder()

            sb.AppendLine("insert Organization (OrganizationName,Parent,Active,EstablishDate,OrgEngName,OrgChnName) ")
            sb.AppendLine("values(@OrganizationName,@Parent,@Active,getdate(),@OrgEngName,@OrgChnName) ")
            sb.AppendLine("select @@identity ")
            Return Int32.Parse(SqlHelper.ExecuteScalar(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams))
        End Function
        Public Sub UpdateOrg(ByVal OrganizationName As String, ByVal Active As String, ByVal OrgEngName As String, ByVal OrgChnName As String, ByVal OrganizationID As String)
            Dim sqlParams As SqlParameter() = New SqlParameter(5) {}
            sqlParams(0) = New SqlParameter("@OrganizationName", SqlDbType.NVarChar)
            sqlParams(0).Value = OrganizationName

            sqlParams(1) = New SqlParameter("@Active", SqlDbType.NVarChar)
            sqlParams(1).Value = Active
            sqlParams(2) = New SqlParameter("@OrgEngName", SqlDbType.NVarChar)
            sqlParams(2).Value = OrgEngName
            sqlParams(3) = New SqlParameter("@OrgChnName", SqlDbType.NVarChar)
            sqlParams(3).Value = OrgChnName

            sqlParams(4) = New SqlParameter("@OrganizationID", SqlDbType.NVarChar)
            sqlParams(4).Value = OrganizationID


            Dim sb As New StringBuilder()
            sb.AppendLine("update Organization set ")
            sb.AppendLine(" OrganizationName = @OrganizationName ")
            sb.AppendLine(",Active = @Active ")
            sb.AppendLine(",OrgEngName = @OrgEngName ")
            sb.AppendLine(",OrgChnName = @OrgChnName ")
            sb.AppendLine("where OrganizationID = @OrganizationID ")

            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)
        End Sub





    End Class
End Namespace